package com.hptpd.questionnairesurvey.utils;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * \* Created with IntelliJ IDEA.
 * \* Date: 2020/1/3
 * \*
 * \* Description:
 * \
 *
 * @author walter_long
 */
public class ExcelUtil {
    /**
     * excel的sheet写入row
     * @param cellVals String[] 数据数组
     * @param index int 行
     */
    public static void sheetRowWrite(@NotNull XSSFSheet sheet, @NotNull Object[] cellVals, int index) {
        if (index < 0) {
            return;
        }
        XSSFRow row = sheet.createRow(index);
        int i = 0;
        for (Object cellVal : cellVals) {
            XSSFCell cell = row.createCell(i++);
            setCell(cell, cellVal);
        }
    }

    /**
     * excel的sheet写入row
     * @param cellVals String[] 数据数组
     * @param index int 行
     */
    public static void sheetRowWrite(@NotNull XSSFSheet sheet, @NotNull List<Object> cellVals, int index) {
        if (index < 0) {
            return;
        }
        XSSFRow row = sheet.createRow(index);
        int i = 0;
        for (Object cellVal : cellVals) {
            XSSFCell cell = row.createCell(i++);
            setCell(cell, cellVal);
        }
    }

    public static void setCell(XSSFCell cell, Object cellVal) {
        if (cellVal instanceof String) {
            cell.setCellValue((String)cellVal);
        } else if (cellVal instanceof Date) {
            cell.setCellValue((Date)cellVal);
        } else if (cellVal instanceof Boolean) {
            cell.setCellValue((Boolean) cellVal);
        } else if (cellVal instanceof Double) {
            cell.setCellValue((Double) cellVal);
        } else if (cellVal instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) cellVal).doubleValue());
        } else if (cellVal instanceof Integer) {
            cell.setCellValue(((Integer) cellVal).doubleValue());
        } else if (cellVal instanceof Long) {
            cell.setCellValue(((Long) cellVal).doubleValue());
        } else if (cellVal instanceof Float) {
            cell.setCellValue(((Float) cellVal).doubleValue());
        } else if (cellVal instanceof Calendar) {
            cell.setCellValue((Calendar) cellVal);
        } else if (cellVal instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellVal);
        }
    }

    /**
     * 导出excel，controller直接返回即可
     *
     * @param xssfWorkbook XSSFWorkbook
     * @param fileName     String
     * @return ResponseEntity<byte       [       ]>
     */
    public static ResponseEntity<byte[]> downloadExcel(@NotNull XSSFWorkbook xssfWorkbook, @NotNull String fileName) {
        ResponseEntity<byte[]> downloadData = null;
        if (fileName.trim().isEmpty()) {
            return null;
        }
        try {
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            xssfWorkbook.write(outputStream);
            downloadData = FileUploadUtil.fileDownloadWarpper(outputStream.toByteArray(),
                    fileName, MediaType.APPLICATION_OCTET_STREAM);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return downloadData;
    }

    /**
     * 行映射到对象
     * 保证对象的field的顺序对应行中的数据顺序
     *
     * @param row        行
     * @param clazz      类
     * @param ignoreProp 忽略的属性
     * @param <T>        <T>
     * @return <T>
     */
    public static <T> T rowMap2Object(@NotNull XSSFRow row, @NotNull Integer cellStart, Class<T> clazz, String... ignoreProp) throws IllegalAccessException, InstantiationException, ParseException {
        T t = clazz.newInstance();
        Field[] fields = t.getClass().getDeclaredFields();
        int index = 0;
        for (Field field : fields) {
            boolean isContinue = false;
            for (String ignore : ignoreProp) {
                if (ignore.equals(field.getName())) {
                    isContinue = true;
                    break;
                }
            }
            if (isContinue) {
                continue;
            }
            Cell cell = row.getCell(index + cellStart);
            fieldAssign(t, field, getCellValue(cell));
            ++index;
        }
        return t;
    }

    /**
     * 获得标题与列索引的映射
     * 用来标记注解中的标题是在哪个单元格中取数据
     * @param sheet XSSFSheet
     * @param titleStart Integer
     * @param titleEnd Integer
     * @param cellStart Integer
     * @return Map<String, Integer>
     */
    public static Map<String, Integer> getTitleMapIndex(@NotNull XSSFSheet sheet,
                                                        @NotNull Integer titleStart,
                                                        @NotNull Integer titleEnd,
                                                        @NotNull Integer cellStart) {
        //作为一个标记，用来确定一列只允许有一个标题
        Map<Integer, String> excelIndexMapTitle = Maps.newLinkedHashMap();
        //回传对象
        Map<String, Integer> dest = Maps.newLinkedHashMap();

        //获取标题与索引的映射
        while (titleStart <= titleEnd) {
            XSSFRow xssfRow = sheet.getRow(titleStart);
            if (null == xssfRow) {
                continue;
            }
            int cellSum = xssfRow.getPhysicalNumberOfCells();
            for (int cellIdx = cellStart; cellIdx < cellSum; cellIdx++) {
                XSSFCell cell = xssfRow.getCell(cellIdx);
                String cellVal = getCellValue(cell).trim();
                if (!StringUtils.isBlank(cellVal)) {
                    if (excelIndexMapTitle.containsKey(cellIdx)) {
                        if (dest.containsKey(cellVal)) {
                            dest.replace(cellVal, cellIdx);
                        } else {
                            dest.remove(excelIndexMapTitle.get(cellIdx));
                            dest.put(cellVal, cellIdx);
                        }
                        excelIndexMapTitle.replace(cellIdx, cellVal);
                    } else {
                        excelIndexMapTitle.put(cellIdx, cellVal);
                        dest.put(cellVal, cellIdx);
                    }
                }
            }
            ++titleStart;
        }
        return dest;
    }

    /**
     * 获取对象所有field，包括基类
     * @param object Object
     * @return Field[]
     */
    public static Field[] getAllFields(Object object) {
        Class clazz = object.getClass();
        List<Field> fieldList = Lists.newLinkedList();
        while (clazz != null) {
            fieldList.addAll(new LinkedList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }

    /**
     * field赋值
     * @param obj Object
     * @param field Field
     * @param value String
     * @throws IllegalAccessException IllegalAccessException
     * @throws ParseException ParseException
     */
    public static void fieldAssign(Object obj, @NotNull Field field, String value) throws IllegalAccessException, ParseException {
        String type = field.getGenericType().toString();
        if ("class java.lang.String".equals(type)) {
            field.setAccessible(true);
            field.set(obj, value);
        }
        if ("class java.lang.Integer".equals(type)) {
            field.setAccessible(true);
            if (StringUtil.isEmpty(value)) {
//                field.set(obj, 0.0);
            } else {
                field.set(obj, Integer.valueOf(value));
            }
        }
        if ("class java.lang.Double".equals(type)) {
            field.setAccessible(true);
            if (StringUtil.isEmpty(value)) {
                field.set(obj, 0.0);
            } else {
                field.set(obj, Double.valueOf(value));
            }
        }
        if ("class java.lang.Float".equals(type)) {
            field.setAccessible(true);
            if (StringUtil.isEmpty(value)) {
//                field.set(obj, 0.0);
            } else {
                field.set(obj, Float.valueOf(value));
            }

        }
        if ("class java.lang.Long".equals(type)) {
            field.setAccessible(true);
            field.set(obj, Long.valueOf(value));
        }
        if ("class java.math.BigDecimal".equals(type)) {
            field.setAccessible(true);
            if (StringUtil.isNumber(value)) {
                field.set(obj, new BigDecimal(value));
            }
        }
        if ("class java.lang.Boolean".equals(type)) {
            field.setAccessible(true);
            field.set(obj, Boolean.valueOf(value));
        }
        if ("class java.util.Date".equals(type)) {
            field.setAccessible(true);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            field.set(obj, simpleDateFormat.parse(value));
        }
    }

    /**
     * 获取cell中的值并返回String类型
     *
     * @param cell Cell
     * @return String类型的cell值
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                // 数字
                case Cell.CELL_TYPE_NUMERIC:
                    // 判断是否为日期类型
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = formatter.format(date);
                    } else {
                        // 有些数字过大，直接输出使用的是科学计数法： 2.67458622E8 要进行处理
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.format(cell.getNumericCellValue());
                        // cellValue = cell.getNumericCellValue() + "";
                    }
                    break;
                // 字符串
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                // Boolean
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                // 公式
                case Cell.CELL_TYPE_FORMULA:
                    //获取计算结果
                    try {
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.format(cell.getNumericCellValue());
//                        cellValue = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                    //直接获取公式
//                    cellValue = cell.getCellFormula() + "";
                    break;
                // 空值
                case Cell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                // 故障
                case Cell.CELL_TYPE_ERROR:
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }
}
